﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using ERP_RORZE_DLL.Product.DAO;
using ERP_RORZE_DLL.Product.database;
using ERP_RORZE_DLL.Product.Base;
using ERP_RORZE_PERMISSION.Base;
using ERP_RORZE_PRODUCT.Product.BusOder;
using ERP_RORZE_SYSTEM;
using Utilities = ERP_RORZE_TOOL.Common.Utilities;

namespace ERP_RORZE_PRODUCT.Product.PartData
{
    public partial class ctrlPartMaterial : DevExpress.XtraEditors.XtraUserControl
    {
        ClsPartDataDAO clsPartdataDAO = new ClsPartDataDAO();
        DataTable dt;
        private LogUtils log;
        ClsPartDataTable obj = new ClsPartDataTable();
        public ctrlPartMaterial()
        {
            InitializeComponent();
            log = new LogUtils();
        }
        void Re_load(object sender, EventArgs e)
        {
            dt = clsPartdataDAO.GetAllPartMaterial();
            dgvPartMaterial.DataSource = dt;
            //gridView1.BestFitColumns();
        }
        private void btnAdd_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            Frm_PartMaterialAdd frmAdd = new Frm_PartMaterialAdd();
            frmAdd.Re_Load += new EventHandler(Re_load);
            frmAdd.Show();
            Cursor.Current = Cursors.Default;
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                Frm_PartMaterialEdit frmEdit = new Frm_PartMaterialEdit();
                frmEdit.Re_Load += new EventHandler(Re_load);
                frmEdit.Bafu = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Bafu").ToString();
                frmEdit.BAnod = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "BAnod").ToString();
                frmEdit.Blast30 = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Blast30").ToString();
                frmEdit.Blast60 = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Blast60").ToString();
                frmEdit.Caciras = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Caciras").ToString();
                frmEdit.Cleanwave = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Cleanwave").ToString();
                frmEdit.Cut = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Cut").ToString();
                frmEdit.HairLine = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "HairLine").ToString();
                frmEdit.HandFinish = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "HandFinish").ToString();
                frmEdit.Helisert = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Helisert").ToString();
                frmEdit.InputDate = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "InputDate").ToString();
                frmEdit.Inside = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Inside").ToString();
                frmEdit.lenght = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "lenght").ToString();
                frmEdit.MaterialID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MaterialID").ToString();
                frmEdit.MaterialType = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MaterialType").ToString();
                frmEdit.Memo = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Memo").ToString();
                frmEdit.Migaki = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Migaki").ToString();
                frmEdit.Note = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Note").ToString();
                frmEdit.Otherpro = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Otherpro").ToString();
                frmEdit.PalCoat = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "PalCoat").ToString();
                frmEdit.PartID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "PartID").ToString();
                frmEdit.Price = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Price").ToString();
                frmEdit.RawMachine = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "RawMachine").ToString();
                frmEdit.Seal = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Seal").ToString();
                frmEdit.SerialNo = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "SerialNo").ToString();
                frmEdit.shape = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "shape").ToString();
                frmEdit.Thickness = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Thickness").ToString();
                frmEdit.VacPac = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "VacPac").ToString();
                frmEdit.WAnod = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "WAnod").ToString();
                frmEdit.width = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "width").ToString();
                frmEdit.Workpiecesize = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Workpiecesize").ToString();
                frmEdit.ID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ID").ToString();
                frmEdit.Show();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            Cursor.Current = Cursors.Default;
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Bạn có thực sự muốn xóa dữ liệu!", "Message", MessageBoxButtons.OKCancel,
                                MessageBoxIcon.Question) == DialogResult.OK)
            {
                obj.ID = int.Parse(gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ID").ToString());
                clsPartdataDAO.DeleteMaterial(obj);

                //write Log
                log.Table = LogConstant.Table.Product.PartMaterial;
                log.Task = LogConstant.Task.Delete;
                log.Description = "PartID: " + gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "PartID");
                log.WriteLog();

            }
            Re_load(sender, e);
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }

        private void dgvPartMaterial_Load(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }

        private void btnAddNew_Click(object sender, EventArgs e)
        {
            btnAdd_Click(sender, e);
        }

        private void btEdit_Click(object sender, EventArgs e)
        {
            btnEdit_Click(sender, e);
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            btnDelete_Click(sender, e);
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            clsConstantsFuction.ExportToExcel(gridView1, 1, true);
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            ImportData();
        }

        private void ImportData()
        {
            INISettings mySetting = new INISettings();
            SqlConnection myConnection = new SqlConnection(mySetting.SQLConnectionString);

            int countOk = 0;
            int countDup = 0;
            int countErr = 0;

            List<Utilities.ListItem> listDup = new List<Utilities.ListItem>();
            List<Utilities.ListItem> listErr = new List<Utilities.ListItem>();
            FileDialog importFile = new OpenFileDialog();
            importFile.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (importFile.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection excelConnection = null;
                try
                {
                    Control parent = progressPanel1.Parent;
                    progressPanel1.Location =
                        new Point(parent.Bounds.X + parent.Bounds.Width / 2 - progressPanel1.Width / 2,
                                  parent.Bounds.Y + parent.Bounds.Height / 2 - progressPanel1.Height / 2);
                    progressPanel1.Visible = true;
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                   importFile.FileName +
                                                   ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd =
                        new OleDbCommand("SELECT [InputDate],[PartID],[MaterialID],[MaterialType],[Workpiecesize]" +
                                         ",[shape],[Thickness],[width],[lenght],[Cut],[RawMachine],[HandFinish]" +
                                         ",[HairLine],[WAnod],[BAnod],[Blast30],[Blast60],[Seal],[Migaki],[Bafu]" +
                                         ",[Cleanwave],[VacPac],[Helisert],[SerialNo],[PalCoat],[Otherpro],[Price]" +
                                         ",[Memo],[Note],[Caciras],[Inside] from [Sheet1$]",
                                         excelConnection);
                    excelConnection.Open();
                    OleDbDataReader dReader;
                    dReader = cmd.ExecuteReader();
                    int i = 0;
                    int line = 1;

                    string inputDate = string.Empty;
                    string PartID = string.Empty;
                    string MaterialID = string.Empty;
                    string MaterialType = string.Empty;
                    string Workpiecesize = string.Empty;
                    string shape = string.Empty;
                    string Thickness = string.Empty;
                    string width = string.Empty;
                    string lenght = string.Empty;
                    string Cut = string.Empty;
                    string RawMachine = string.Empty;
                    string HandFinish = string.Empty;
                    string HairLine = string.Empty;
                    string WAnod = string.Empty;
                    string BAnod = string.Empty;
                    string Blast30 = string.Empty;
                    string Blast60 = string.Empty;
                    string Seal = string.Empty;
                    string Migaki = string.Empty;
                    string Bafu = string.Empty;
                    string Cleanwave = string.Empty;
                    string VacPac = string.Empty;
                    string Helisert = string.Empty;
                    string SerialNo = string.Empty;
                    string PalCoat = string.Empty;
                    string Otherpro = string.Empty;
                    string Price = string.Empty;
                    string Memo = string.Empty;
                    string Note = string.Empty;
                    string Caciras = string.Empty;
                    string Inside = string.Empty;


                    myConnection.Open();
                    while (dReader.Read())
                    {
                        bool exist = false;
                        line++;

                        //check null
                        try
                        {
                            if (
                                dReader["InputDate"].ToString().Trim().Equals(string.Empty)
                                && dReader["PartID"].ToString().Trim().Equals(string.Empty)
                                && dReader["MaterialID"].ToString().Trim().Equals(string.Empty)
                                && dReader["MaterialType"].ToString().Trim().Equals(string.Empty)
                                && dReader["Workpiecesize"].ToString().Trim().Equals(string.Empty)
                                && dReader["shape"].ToString().Trim().Equals(string.Empty)
                                && dReader["Thickness"].ToString().Trim().Equals(string.Empty)
                                && dReader["width"].ToString().Trim().Equals(string.Empty)
                                && dReader["lenght"].ToString().Trim().Equals(string.Empty)
                                && dReader["Cut"].ToString().Trim().Equals(string.Empty)
                                && dReader["RawMachine"].ToString().Trim().Equals(string.Empty)
                                && dReader["HandFinish"].ToString().Trim().Equals(string.Empty)
                                && dReader["HairLine"].ToString().Trim().Equals(string.Empty)
                                && dReader["WAnod"].ToString().Trim().Equals(string.Empty)
                                && dReader["BAnod"].ToString().Trim().Equals(string.Empty)
                                && dReader["Blast30"].ToString().Trim().Equals(string.Empty)
                                && dReader["Blast60"].ToString().Trim().Equals(string.Empty)
                                && dReader["Seal"].ToString().Trim().Equals(string.Empty)
                                && dReader["Migaki"].ToString().Trim().Equals(string.Empty)
                                && dReader["Bafu"].ToString().Trim().Equals(string.Empty)
                                && dReader["Cleanwave"].ToString().Trim().Equals(string.Empty)
                                && dReader["VacPac"].ToString().Trim().Equals(string.Empty)
                                && dReader["Helisert"].ToString().Trim().Equals(string.Empty)
                                && dReader["SerialNo"].ToString().Trim().Equals(string.Empty)
                                && dReader["PalCoat"].ToString().Trim().Equals(string.Empty)
                                && dReader["Otherpro"].ToString().Trim().Equals(string.Empty)
                                && dReader["Price"].ToString().Trim().Equals(string.Empty)
                                && dReader["Memo"].ToString().Trim().Equals(string.Empty)
                                && dReader["Note"].ToString().Trim().Equals(string.Empty)
                                && dReader["Caciras"].ToString().Trim().Equals(string.Empty)
                                && dReader["Inside"].ToString().Trim().Equals(string.Empty)
                                )
                            {
                                countErr++;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"].ToString(), "Null line"));
                                exist = true;
                            }
                        }
                        catch (Exception e)
                        {
                            countErr++;
                            listErr.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"].ToString(), e.Message));
                            exist = true;
                        }

                        //check valid data
                        if (!exist)
                        {
                            try
                            {
                                inputDate = (dReader["InputDate"].ToString().Equals(string.Empty))
                                                ? "null"
                                                : "'" + DateTime.Parse(dReader["InputDate"].ToString()).ToString("yyyy/MM/dd") + "'";

                                PartID = (dReader["PartID"].ToString().Equals(string.Empty))
                                             ? "null"
                                             : "N'" + dReader["PartID"].ToString() + "'";
                                MaterialID = (dReader["MaterialID"].ToString().Equals(string.Empty))
                                                ? "null"
                                                : "N'" + dReader["MaterialID"].ToString() + "'";
                                MaterialType = (dReader["MaterialType"].ToString().Equals(string.Empty))
                                               ? "null"
                                               : "N'" + dReader["MaterialType"].ToString() + "'";
                                Workpiecesize = (dReader["Workpiecesize"].ToString().Equals(string.Empty))
                                                 ? "null"
                                                 : "N'" + dReader["Workpiecesize"].ToString() + "'";
                                shape = (dReader["shape"].ToString().Equals(string.Empty))
                                            ? "null"
                                            : "N'" + dReader["shape"].ToString() + "'";
                                Thickness = (dReader["Thickness"].ToString().Equals(string.Empty))
                                              ? "null"
                                              : dReader["Thickness"].ToString();
                                width = (dReader["width"].ToString().Equals(string.Empty))
                                          ? "width"
                                          : dReader["width"].ToString();
                                lenght = (dReader["lenght"].ToString().Equals(string.Empty))
                                              ? "null"
                                              : dReader["lenght"].ToString();
                                Cut = (dReader["Cut"].ToString().Equals(string.Empty))
                                              ? "null"
                                              : "'" + dReader["Cut"].ToString() + "'";
                                RawMachine = (dReader["RawMachine"].ToString().Equals(string.Empty))
                                                ? "null"
                                                : "'" + dReader["RawMachine"].ToString() + "'";
                                HandFinish = (dReader["HandFinish"].ToString().Equals(string.Empty))
                                               ? "null"
                                               : "'" + dReader["HandFinish"].ToString() + "'";
                                HairLine = (dReader["HairLine"].ToString().Equals(string.Empty))
                                              ? "null"
                                              : "'" + dReader["HairLine"].ToString() + "'";
                                WAnod = (dReader["WAnod"].ToString().Equals(string.Empty))
                                               ? "null"
                                               : "'" + dReader["WAnod"].ToString() + "'";
                                BAnod = (dReader["BAnod"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "'" + dReader["BAnod"].ToString() + "'";
                                Blast30 = (dReader["Blast30"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "'" + dReader["Blast30"].ToString() + "'";
                                Blast60 = (dReader["Blast60"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "'" + dReader["Blast60"].ToString() + "'";
                                Seal = (dReader["Seal"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "'" + dReader["Seal"].ToString() + "'";
                                Migaki = (dReader["Migaki"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "'" + dReader["Migaki"].ToString() + "'";
                                Bafu = (dReader["Bafu"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "'" + dReader["Bafu"].ToString() + "'";
                                Cleanwave = (dReader["Cleanwave"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "'" + dReader["Cleanwave"].ToString() + "'";
                                VacPac = (dReader["VacPac"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "'" + dReader["VacPac"].ToString() + "'";
                                Helisert = (dReader["Helisert"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "'" + dReader["Helisert"].ToString() + "'";
                                SerialNo = (dReader["SerialNo"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "'" + dReader["SerialNo"].ToString() + "'";
                                PalCoat = (dReader["PalCoat"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "'" + dReader["PalCoat"].ToString() + "'";
                                Otherpro = (dReader["Otherpro"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "N'" + dReader["Otherpro"].ToString() + "'";
                                Price = (dReader["Price"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "N'" + dReader["Price"].ToString() + "'";
                                Memo = (dReader["Memo"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "N'" + dReader["Memo"].ToString() + "'";
                                Note = (dReader["Note"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "N'" + dReader["Note"].ToString() + "'";
                                Caciras = (dReader["Caciras"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "'" + dReader["Caciras"].ToString() + "'";
                                Inside = (dReader["Inside"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "'" + dReader["Inside"].ToString() + "'";

                            }
                            catch (Exception ex)
                            {
                                exist = true;
                                listErr.Add(new Utilities.ListItem(line.ToString(),
                                                                   dReader["PartID"].ToString(),
                                                                   ex.Message));
                            }
                        }

                        //check duplicate
                        if (!exist)
                        {
                            try
                            {
                                SqlCommand cmd_sql = myConnection.CreateCommand();
                                cmd_sql.CommandText = "SELECT * FROM [242_PartData] WHERE [PartID]='" +
                                                      dReader["PartID"].ToString() + "'";
                                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                                if (dataReader.Read())
                                {

                                    exist = true;
                                    countDup++;
                                    listDup.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"].ToString(), "Duplicate"));
                                }
                                dataReader.Close();
                                cmd_sql.Dispose();
                            }
                            catch (Exception e)
                            {
                                exist = true;
                                listDup.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"].ToString(), e.Message));
                            }
                        }

                        //Insert to database
                        if (!exist)
                        {
                            try
                            {
                                string sql = "INSERT INTO [242_PartData] ([InputDate],[PartID],[MaterialID],[MaterialType],[Workpiecesize],[shape]" +
                                             ",[Thickness],[width],[lenght],[Cut],[RawMachine],[HandFinish],[HairLine]" +
                                             ",[WAnod],[BAnod],[Blast30],[Blast60],[Seal],[Migaki],[Bafu],[Cleanwave]" +
                                             ",[VacPac],[Helisert],[SerialNo],[PalCoat],[Otherpro],[Price],[Memo],[Note]" +
                                             ",[Caciras],[Inside])"
                                            + " VALUES (" + inputDate + ", " + PartID + ", " + MaterialID + "," + MaterialType + "," + Workpiecesize + ", " + shape + ", "
                                            + Thickness + ", " + width + ", " + lenght + " ," + Cut + ", " + RawMachine + ", " + HandFinish + ", " + HairLine + ", "
                                            + WAnod + ", " + BAnod + ", " + Blast30 + ", " + Blast60 + ", " + Seal + ", " + Migaki + ", " + Bafu + ", " + Cleanwave + ", "
                                            + VacPac + ", " + Helisert + ", " + SerialNo + ", " + PalCoat + ", " + Otherpro + ", " + Price + ", " + Memo + ", " + Note + ", "
                                            + Caciras + ", " + Inside + ")";
                                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);

                                cmd_sql.ExecuteNonQuery();
                                cmd_sql.Dispose();
                                countOk++;

                                //write Log
                                log.Table = LogConstant.Table.Product.PartMaterial;
                                log.Task = LogConstant.Task.Import;
                                log.Description = "InputDate: " + inputDate + "; PartID: " + PartID;
                                log.WriteLog();

                            }
                            catch (Exception ex)
                            {
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"].ToString(), ex.Message));
                            }

                        }
                    }
                    myConnection.Close();
                    progressPanel1.Visible = false;
                    excelConnection.Close();
                    //MessageBox.Show(i + " records have been imported. \n" + countDup.ToString() + " record(s) duplicated (Lines: " + lineDup + ")\nLines have error OptionID: " + lineNo);
                    //LoadData();
                    Frm_ImportReport report = new Frm_ImportReport(countOk, listDup, listErr, "PartMaterial", "PartID");
                    report.StartPosition = FormStartPosition.CenterScreen;
                    report.Show();
                    report.Activate();
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    progressPanel1.Visible = false;
                    MessageBox.Show(ex.Message);
                }

            }
        }


    }
}
